CREATE PROCEDURE RRHH_BUSCAR_EMPLEADOID_SERVICIO_L
@IDEMPLEADO INTEGER,
@HORA DATETIME,
@FECHA DATETIME,
@IDSERVICIO INTEGER
AS

BEGIN TRANSACTION

DECLARE @IDES INTEGER
DECLARE @IDAC INTEGER
DECLARE @DURA INTEGER
DECLARE @HORAFIN DATETIME

BEGIN TRY
	SET @IDES = (SELECT IDESTADO FROM ESTADOEMPLEADO
				WHERE descripcion = 'Eliminado')
	SET @DURA = (SELECT duracionHoras FROM SERVICIO
				 WHERE idServicio = @IDSERVICIO)
	SET @HORAFIN = @HORA
	
	SET @HORAFIN = DATEADD(HOUR, @DURA, @HORAFIN)
								
	SELECT E.idEmpleado, E.nombre, E.apPaterno, E.apMaterno, E.idTipoDoc, 
	       E.docIdentidad, E.fecNacimiento, E.sexo, E.direccion, E.telCasa,
	       E.telCelular, E.telEmergencia, E.contactoEmergencia, E.idArea, 
	       E.idCargo, E.idUsuario, E.idHorario, E.idEstado    
	FROM EMPLEADO E, EMPLEADOXSERVICIO X
	WHERE E.idEmpleado = @IDEMPLEADO AND
		  E.idEstado <> @IDES AND
		  X.idEmpleado = E.idEmpleado AND
		  X.idServicio = @IDSERVICIO AND
		  (SELECT COUNT(*) 
		   FROM AGENDAACTIVIDAD A
			 WHERE A.idEmpleado = E.idEmpleado AND 
			 CAST(A.fecha AS DATE) = CAST(@FECHA AS DATE) 
			 AND
			 (
			 ((CAST(@HORA AS TIME(7)) >= CAST(A.horaInicioAct AS TIME(7)))
			 AND (CAST(@HORA AS TIME(7)) <= CAST(A.horaFinActividad AS TIME(7))) 
			 AND (CAST(@HORAFIN AS TIME(7)) >= CAST(A.horaInicioAct AS TIME(7))) 
			 AND (CAST(@HORAFIN AS TIME(7)) <= CAST(A.horaFinActividad AS TIME(7))))
			 OR
			 ((CAST(@HORA AS TIME(7)) > CAST(A.horaInicioAct AS TIME(7))) 
			 AND (CAST(@HORA AS TIME(7)) < CAST(A.horaFinActividad AS TIME(7))) 
			 AND (CAST(@HORAFIN AS TIME(7)) > CAST(A.horaFinActividad AS TIME(7))))
		     OR
			 ((CAST(@HORA AS TIME(7)) < CAST(A.horaInicioAct AS TIME(7))) 
			 AND (CAST(@HORAFIN AS TIME(7)) > CAST(A.horaInicioAct AS TIME(7)))
			 AND (CAST(@HORAFIN AS TIME(7)) < CAST(A.horaFinActividad AS TIME(7))))
			 OR
			 ((CAST(@HORA AS TIME(7)) < CAST(A.horaInicioAct AS TIME(7))) 
			 AND (CAST(@HORAFIN AS TIME(7)) > CAST(A.horaFinActividad AS TIME(7)))) 
			 )
			 AND A.ESTADO = 1
			 )	= 0  
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
